package com.wits.qso.dao.impl;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Component;

import com.wits.platform.dao.impl.BaseDaoImpl;
import com.wits.qso.dao.custom.BtableDaoCustom;
import com.wits.qso.entity.Btable;
import com.wits.qso.entity.Cls;
import com.wits.qso.entity.Course;
import com.wits.qso.entity.Croom;
import com.wits.qso.entity.Dept;
import com.wits.qso.entity.Grade;
import com.wits.qso.entity.Teacher;

@Component
public class BtableDaoImpl extends BaseDaoImpl<Btable, String> implements
		BtableDaoCustom {

	private SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
	public int deleteByCroom(String crmId) {
		Query query = em.createQuery( "DELETE FROM Btable o WHERE o.crmid.crmId = :crmId" );
		query.setParameter( "crmId", crmId );
		
		return query.executeUpdate();
	}
	
	public boolean isEixtClass(Cls claid,Date start,Date end){
		Query query = em.createQuery( " FROM Btable o WHERE o.claid.clsId = :claid AND " +
				"((:start>o.time_start AND :start<o.time_end) OR (:end>o.time_start AND :end<o.time_end))" );
		query.setParameter( "claid", claid.getClsId() );
		query.setParameter( "start", start );
		query.setParameter( "end", end );
		List list=query.getResultList();
		if(list!=null && list.size()>0)
			return true;
		return false;
	}
	
//	public int update(Croom room,String courseId,String dptId,String teaId,String gradeId,String classId,
//			String week_day,Date start,Date end) {
//		Query query = em.createNativeQuery( "UPDATE Btable SET couid ='"+courseId+"',teaid='"+teaId+"'," +
//				"gradeid='"+gradeId+"',dptid='"+dptId+"',claid='"+classId+"'," );
//		query.setParameter( "courseId", courseId );
//		query.setParameter( "teaId", teaId );
//		query.setParameter( "gradeId", gradeId );
//		query.setParameter( "dptId", dptId );
//		query.setParameter( "classId", classId );
//		
//		return query.executeUpdate();
//	}
	
	public List<Btable> findByCroomAndWeDay(String weday, String crmId) {
		List<Btable> result = new ArrayList<Btable>();
		
		Query query = em.createQuery( "SELECT DISTINCT o.calss FROM Btable o " +
				"WHERE o.crmid.crmId = :crmId AND o.weday = :weday" );
		query.setParameter( "weday", weday );
		query.setParameter( "crmId", crmId ); 
		List<String> calsses = query.getResultList();
		
		for(String calss : calsses) {
			query = em.createQuery( "FROM Btable o WHERE o.crmid.crmId = :crmId " +
					"AND o.calss = :calss AND o.weday = :weday ORDER BY o.time_start ASC" );
			query.setParameter( "weday", weday );
			query.setParameter( "calss", calss );
			query.setParameter( "crmId", crmId );
			query.setMaxResults( 1 );
			
			result.add( (Btable) query.getSingleResult() );
		}
	
		return result;
	}
	
	//找在所有在 btale 的 croom 
	public List<Croom> findAliveCroom() {
		Query query = em.createQuery( "SELECT DISTINCT o.crmid FROM Btable o" );
		return (List<Croom>) query.getResultList();
	}

	public List<Btable> findByCroom(String crmId) {
		Query query = em.createQuery( "FROM Btable o WHERE o.crmid.crmId = :crmId" );
		query.setParameter( "crmId", crmId );
		
		return query.getResultList();
	}
	
	public List<Btable> findByCroom(String crmId,Date start,Date end) {
		Query query = em.createQuery( " FROM Btable o WHERE o.crmid.crmId = :crmId " +
				" AND YEARWEEK(date_format(o.time_start,'%Y-%m-%d')) >=YEARWEEK(:start) AND YEARWEEK(date_format(o.time_end,'%Y-%m-%d'))<=YEARWEEK(:end)" );
		query.setParameter( "crmId", crmId );
		query.setParameter( "start", start);
		query.setParameter( "end", end);
		
		return query.getResultList();
	}
	
	/**
	 * 查询出要更新的课表数据
	 */
	public List<Btable> findByCroom(Croom room,String weday,String cla) {
		Query query = em.createQuery( "FROM Btable o WHERE o.weday = :weday AND o.crmid=:room " +
				"AND o.calss = :cla AND o.time_start >=:start AND o.time_end<=:end");
		query.setParameter( "room", room );
		query.setParameter( "weday", weday );
		query.setParameter( "cla", cla );
		query.setParameter( "start", room.getDateF());
		query.setParameter( "end", room.getDateT());
		return query.getResultList();
	}

	@Override
	public List<Btable> findByParameter(Croom room, Course courId,
			Dept deptId,Teacher terId,Grade gradeId,Cls calId,String weday,String cal) {
		Query query = em.createQuery( "FROM Btable o WHERE o.crmid = :crmid " +
				"AND o.claid = :calId AND o.couid =:courId AND o.teaid =:terId " +
				"AND o.dptid = :deptId AND o.gradeid =:gradeId AND o.weday = :weday AND o.calss =:calss");
		query.setParameter( "crmid", room);
		query.setParameter( "courId", courId );
		query.setParameter( "deptId", deptId );
		query.setParameter( "terId", terId);
		query.setParameter( "gradeId", gradeId);
		query.setParameter( "calId", calId);
		query.setParameter( "weday", weday);
		query.setParameter( "calss", cal);
		return query.getResultList();
	}

    public Btable findByIdAndTeacher(String id, Teacher teacher) {
    	Btable result = null;
    	
    	try {
    		Query query = em.createQuery( "FROM Btable o WHERE o.id = :id AND o.teaid = :teacher" );
    		query.setParameter( "id", id );
    		query.setParameter( "teacher", teacher );
    		
    		result = (Btable) query.getSingleResult();
    	} catch (NoResultException cause) {
    	}
    	
    	return result;
    }

    public List<Btable> findByTeacher(Teacher teacher) {
    	Query query = em.createQuery( "FROM Btable o WHERE o.teaid = :teacher" );
		query.setParameter( "teacher", teacher );
		
		return query.getResultList();
    }

	@Override
	public List<Btable> findByclaId(Cls claId) {
		Query query = em.createQuery( "FROM Btable o WHERE o.claid = :claid" );
		query.setParameter( "claid", claId );
		
		return query.getResultList();
	}
	
	/**
	 * 获取某一班级在一周内的课表
	 */
	public List<Btable> findByclaId(Cls claId,Date start,Date end){
		if(start!=null && end!=null){
			Query query = em.createQuery( "FROM Btable o WHERE o.claid = :claid " +
					"AND YEARWEEK(date_format(o.time_start,'%Y-%m-%d')) >=YEARWEEK(:start) AND YEARWEEK(date_format(o.time_end,'%Y-%m-%d'))<=YEARWEEK(:end)" );
			query.setParameter( "claid", claId );
			query.setParameter( "start", start);
			query.setParameter( "end", end);
			return query.getResultList();
		}else{
			return findByclaId(claId);
		}
	}
	
	@Override
	public List<Btable> findByclaIdAndCourseId(Cls claId, Course course) {
		Query query = em.createQuery( "FROM Btable o WHERE o.claid = :claid AND o.couid=:couid" );
		query.setParameter( "claid", claId );
		query.setParameter("couid", course);
		
		return query.getResultList();
	}
	
	public Btable findByclaIdAndId(String id,Cls claId){
		Query query = em.createQuery( "FROM Btable o WHERE o.id=:id AND o.claid = :claid" );
		query.setParameter( "id", id );
		query.setParameter( "claid", claId );
		
		return (Btable) query.getSingleResult();
	}

	@Override
	public List<Btable> findByWedayAndClass(String weday, String cla,Croom room) {
		Query query = em.createQuery( "FROM Btable o WHERE o.calss = :calss AND o.weday = :weday AND o.crmid != :room" );
		query.setParameter( "calss", cla );
		query.setParameter( "weday", weday );
		query.setParameter( "room", room );
		
		return query.getResultList();
	}

	@Override
	public List<Btable> findByCroomAndWedayTF(Croom room, int wedayStart,
			int wedayEnd) {
		Query query = em.createQuery( "FROM Btable o WHERE cast(o.weday as integer) >= :wedayStart " +
				"AND cast(o.weday as integer) <= :wedayEnd AND o.crmid = :room" );
		query.setParameter( "wedayStart", wedayStart );
		query.setParameter( "wedayEnd", wedayEnd );
		query.setParameter( "room", room );
		
		return query.getResultList();
	}
	
	public Btable findByCurrentTime(Date currentTime) {
		Btable result = null;
		
		try {
			Query query = em.createQuery( "FROM Btable o WHERE o.time_start <= :current AND o.time_end > :current" );
			query.setParameter( "current", currentTime );
		
			result =(Btable) query.getSingleResult();
		} catch(NoResultException cause) {}
		
		return result;
	}
	@Override
	public List<Btable> findIsSettedByCroom(Croom room) {
		Query query = em.createQuery( "FROM Btable o WHERE o.crmid = :crmid " +
				"AND o.claid != null AND o.couid !=null AND o.teaid !=null " +
				"AND o.dptid !=null AND o.gradeid !=null");
		query.setParameter( "crmid", room);
		return query.getResultList();
	}

}
